﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Collections;

public partial class Pages_TableContent : DatabasePage
{
    
    #region Properties

    string TableName
    {
        get
        {
            return Request.QueryString["TableName"];
        }
    }

    private string OrderByColumn
    {
        get
        {
            if (ViewState["OrderByColumn"] == null)
            {
                return null;
            }
            else
            {
                return ViewState["OrderByColumn"].ToString();
            }
        }
        set
        {
            ViewState["OrderByColumn"] = value;
        }
    }

    private string OrderByDirection
    {
        get
        {
            if (ViewState["OrderByDirection"] == null)
            {
                ViewState["OrderByDirection"] = "DESC";
            }
            return ViewState["OrderByDirection"].ToString();
        }
        set
        {
            ViewState["OrderByDirection"] = value;
        }
    }

    #endregion

    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (ConnectionId == 4)
            {
                radQuery.Enabled = false;
            }
            lblMessage.Text = string.Empty;
            if (!IsPostBack)
            {
                SetContentHeader();
                LoadLastSearchText();
                LoadData();

                if (gvTableContent.DataKeyNames.Length > 0)
                {
                    hypNewItem.NavigateUrl = string.Format("ItemEdit.aspx?TableName={0}&IdColumn={1}", TableName, gvTableContent.DataKeyNames[0]);
                    hypNewItem.Text = string.Format("New {0}", TableName);
                }
            }
            txtSearch.Focus();
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
    }

    private void SetContentHeader()
    {
        OutlookMaster om = (OutlookMaster)Master;
        om.ContentHeader.Text = TableName;
    }

    private void LoadLastSearchText()
    {
        if (Session["SearchText"] != null && Request.UrlReferrer != null && Request.UrlReferrer.Segments[Request.UrlReferrer.Segments.Length - 1] == "ItemEdit.aspx")
        {
            txtSearch.Text = Session["SearchText"].ToString();
        }
    }

    private void LoadData()
    {
        lblMessage.Text = string.Empty;
        try
        {
            //Include search text in SELECT
            string selectStr = BuildSelect();
            if (!IsPostBack)
            {
                txtQuery.Text = selectStr;
            }
            DataTable dt = DataAccess.GetDataTableSchema(ConnectionString, selectStr);

            //Referencing columns values
            GetMappingColumns(dt);

            if (OrderByColumn != null)
            {
                dt.DefaultView.Sort = string.Format("{0} {1}", OrderByColumn, OrderByDirection);
            }
            gvTableContent.DataKeyNames = new string[] { dt.PrimaryKey[0].ColumnName };

            gvTableContent.DataSource = dt;
            gvTableContent.DataBind();

            string idColumnType;
            if (dt.PrimaryKey[0].DataType != typeof(int))
            {
                idColumnType = "string";
            }
            else
            {
                idColumnType = "int";
            }
            hypNew.NavigateUrl = String.Format("ItemEdit.aspx?TableName={0}&IdColumn={1}&IdCt={2}", TableName, gvTableContent.DataKeyNames[0], idColumnType);

            if (dt.Rows.Count == 100)
            {
                lblMessage.Text = "Only 100 rows retrieved, there may be more...";
            }
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
            gvTableContent.DataSource = null;
            gvTableContent.DataBind();
        }
    }

    private string BuildSelect()
    {
        try
        {
            string searchText = DataAccess.ClearStringForSQL(txtSearch.Text);
            //Fist search, get all
            string selectStr = string.Format("SELECT TOP 100 * FROM {0} ORDER BY 1 DESC", TableName);
            if (!IsPostBack)
            {
                return selectStr;
            }
            
            // Build select, All columns search or Query
            if (radAllColumnsSearch.Checked && txtSearch.Text != string.Empty)
            {
                Session["SearchText"] = txtSearch.Text;

                selectStr = string.Format("SELECT TOP 1 * FROM {0}", TableName);
                DataTable dt = DataAccess.GetDataTableSchema(ConnectionString, selectStr);

                selectStr = string.Format("SELECT TOP 100 * FROM {0} WHERE 1=2 ", TableName);

                foreach (DataColumn dc in dt.Columns)
                {
                    if (dc.DataType == typeof(string))
                    {
                        selectStr += string.Format("OR {0} LIKE '%{1}%'", dc.ColumnName, searchText);
                    }
                    else if (dc.DataType == typeof(int))
                    {
                        decimal d;
                        if (decimal.TryParse(searchText, out d))
                        {
                            selectStr += string.Format("OR {0} = {1}", dc.ColumnName, searchText);
                        }
                    }
                }

                //No conditions for OR, force one
                if (selectStr.IndexOf("OR") == -1)
                {
                    selectStr += "OR 1=1";
                }

                if (OrderByColumn == null)
                {
                    OrderByColumn = dt.Columns[0].ColumnName;
                }
                return selectStr;
            }
            else //if (radQuery.Checked)
            {
                return txtQuery.Text;
            }
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
            gvTableContent.DataSource = null;
            gvTableContent.DataBind();
            return string.Empty;
        }
    }

    protected void gvTableContent_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            lblMessage.Text = string.Empty;
            Response.Redirect(String.Format("ItemEdit.aspx?TableName={0}&IdColumn={1}&IdValue={2}", TableName, gvTableContent.DataKeyNames[0], gvTableContent.SelectedValue), false);
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
    }

    protected void gvTableContent_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            lblMessage.Text = string.Empty;
            gvTableContent.PageIndex = e.NewPageIndex;
            LoadData();
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
    }
    protected void gvTableContent_Sorting(object sender, GridViewSortEventArgs e)
    {
        try
        {
            lblMessage.Text = string.Empty;
            OrderByColumn = e.SortExpression;
            if (OrderByDirection == "ASC")
            {
                OrderByDirection = "DESC";
            }
            else
            {
                OrderByDirection = "ASC";
            }
            LoadData();
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
    }
    protected void btnSearch_Click(object sender, ImageClickEventArgs e)
    {
        LoadData();
    }

    private void GetMappingColumns(DataTable dt)
    {
        //Get mapping columns to add
        ArrayList columnsToAdd = new ArrayList();
        foreach (DataColumn dc in dt.Columns)
        {
            DataRow mappingRow = MappingRow(TableName, dc.ColumnName);
            if (mappingRow != null)
            {
                columnsToAdd.Add(mappingRow["ReferenceDisplayColumn"]);
            }
        }

        //Add mapping columns
        foreach (object o in columnsToAdd)
        {
            dt.Columns.Add(o.ToString());
        }

        //Get mapping data
        foreach (DataRow row in dt.Rows)
        {
            foreach (DataColumn dc in dt.Columns)
            {
                DataRow mappingRow = MappingRow(TableName, dc.ColumnName);
                if (mappingRow != null)
                {
                    row[mappingRow["ReferenceDisplayColumn"].ToString()] = GetValue(mappingRow["ReferenceObject"].ToString(), mappingRow["ReferenceDisplayColumn"].ToString(), mappingRow["ReferenceIdColumn"].ToString(), row[dc].ToString());
                    //columnsToAdd.Add(mappingRow["ReferenceTableDisplayColumn"]);
                }
            }
        }
    }

    public DataRow MappingRow(string tableName, string columnName)
    {
        string selectStr = string.Format("SELECT * FROM ColumnMapping WHERE ConnectionId={0} AND TableName = '{1}' AND TableColumnName = '{2}'", ConnectionId, tableName, columnName);
        DataRow row = DataAccess.GetDataRow(DataAccess.ConfigurationConnectionString, selectStr);
        return row;
    }

    public object GetValue(string tableName, string columnName, string idColumn, string id)
    {
        if (string.IsNullOrEmpty(id))
        {
            return string.Empty;
        }
        else
        {
            string selectValue = string.Format("SELECT {0} FROM {1} WHERE {2} = {3}", columnName, tableName, idColumn, id);
            return DataAccess.GetValue(ConnectionString, selectValue);
        }
    }
   
    protected void radQuery_CheckedChanged(object sender, EventArgs e)
    {
        radAllColumnsSearch.Checked = false;
        txtSearch.Enabled = false;
        txtQuery.Enabled = true;
        txtQuery.Focus();
    }
    protected void radAllColumnsSearch_CheckedChanged(object sender, EventArgs e)
    {
        radQuery.Checked = false;
        txtQuery.Enabled = false;
        txtSearch.Enabled = true;
        txtSearch.Focus();
    }
}